﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using SqlCon;
using System.Collections;

namespace yfxt
{
    public partial class ms_tycl : Form
    {
        public ms_tycl()
        {
            InitializeComponent();
        }


        SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);

        SqlDataAdapter da;

        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)//键盘回车事件
            {

                if (conn.State != ConnectionState.Open) { conn.Open(); }

                DataTable dt = new DataTable();

                if (textBox1.Text.Trim() == "")
                {
                    MessageBox.Show("您还没有输入处方号码或发票号码，请输入再查询！");
                    return;
                }
                if (textBox1.Text.Length == 2)
                {
                    string s = "select cfhm,brxm from ms_cf01 where cfhm=" + textBox1.Text.ToString() + " ";

                    SqlCommand cmd = new SqlCommand(s, conn);
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


                    if (!dr.HasRows)
                    {
                        MessageBox.Show("没有这个处方号码，请认真检查输入正确！！");

                        dr.Close();
                    }
                    else { conn.Close(); }

                    if (conn.State != ConnectionState.Open) { conn.Open(); }
                    da = new SqlDataAdapter(s, conn);

                    da.Fill(dt);

                }
                else
                {
                    string s = "select cfhm ,brxm from ms_cf01 where fphm=" + textBox1.Text.ToString() + " ";


                    SqlCommand cmd = new SqlCommand(s, conn);

                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


                    if (!dr.HasRows)
                    {
                        MessageBox.Show("没有这个发票号码，请认真检查输入正确！！");

                        dr.Close();

                    }
                    else { conn.Close(); }


                    if (conn.State != ConnectionState.Open) { conn.Open(); }

                    da = new SqlDataAdapter(s, conn);

                    da.Fill(dt);

                }

                dataGridView1.DataSource = dt;

                dataGridView1.Columns[0].HeaderCell.Value = "处方号码";

                dataGridView1.Columns[1].HeaderCell.Value = "病人姓名";





                ////取得一些数据填充datagridview2上面的textbox内容  不能删
                //using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                //{
                //    if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }
                //    string s_01 = "select @brxm=ms_mzxx.brxm,@ysxm=ygxm,@kfrq=kfrq,@sfrq=sfrq,@cfzjg=sum(ypsl*ypdj) " +
                //                  "from ms_cf01 ,gy_ygdm ,ms_cf02 ,ms_mzxx " +
                //                  " where ms_cf01.cfsb=ms_cf02.cfsb and  ms_cf01.ysdm=gy_ygdm.ygdm and  ms_cf01.fphm=ms_mzxx.fphm" +
                //                  " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                //                  " group by ms_mzxx.brxm,ygxm,kfrq,sfrq";

                //    SqlCommand cmd = new SqlCommand(s_01, conn_01);

                //    SqlParameter[] sp = new SqlParameter[5];

                //    sp[0] = cmd.Parameters.Add("@brxm", SqlDbType.NVarChar, 40);
                //    sp[1] = cmd.Parameters.Add("@ysxm", SqlDbType.NVarChar, 40);
                //    sp[2] = cmd.Parameters.Add("@kfrq", SqlDbType.DateTime);
                //    sp[3] = cmd.Parameters.Add("@sfrq", SqlDbType.DateTime);
                //    sp[4] = cmd.Parameters.Add("@cfzjg", SqlDbType.Money, 40);

                //    sp[0].Direction = ParameterDirection.Output;
                //    sp[1].Direction = ParameterDirection.Output;
                //    sp[2].Direction = ParameterDirection.Output;
                //    sp[3].Direction = ParameterDirection.Output;
                //    sp[4].Direction = ParameterDirection.Output;


                //    cmd.ExecuteNonQuery();

                //    textBox2.Text = sp[0].Value.ToString();
                //    textBox3.Text = sp[1].Value.ToString();
                //    textBox4.Text = sp[2].Value.ToString();
                //    textBox6.Text = sp[3].Value.ToString();
                //    textBox5.Text = sp[4].Value.ToString();
                //}

                //如果取得来的数据是一行的话 就可以这样做 这样的代码比较简短

                using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                {
                    if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }
                    string s_01 = "select ms_mzxx.brxm as brxm,ygxm,kfrq,sfrq,sum(ypsl*ypdj) as cfzjg " +
                                  "from ms_cf01 ,gy_ygdm ,ms_cf02 ,ms_mzxx " +
                                  " where ms_cf01.cfsb=ms_cf02.cfsb and  ms_cf01.ysdm=gy_ygdm.ygdm and  ms_cf01.fphm=ms_mzxx.fphm" +
                                  " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                                  " group by ms_mzxx.brxm,ygxm,kfrq,sfrq";

                    SqlCommand cmd = new SqlCommand(s_01, conn_01);

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {

                        textBox2.Text = dr["brxm"].ToString();
                        textBox3.Text = dr["ygxm"].ToString();
                        textBox4.Text = dr["kfrq"].ToString();
                        textBox6.Text = dr["sfrq"].ToString();
                        textBox5.Text = dr["cfzjg"].ToString();
                    }
                }





            }

        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            
            string s = "select yk_typk.ypmc,ms_cf02.yfgg ,yk_typk.ypdw, yk_cddz.cdmc,ms_cf02.ypdj,ms_cf02.ypsl," +
                       "sum(ms_cf02.ypsl*ms_cf02.ypdj) as ypjg ,ms_cf02.sbxh"+
                       " from ms_cf01,ms_cf02,yk_typk,yk_ypcd,yk_cddz where ms_cf01.cfsb=ms_cf02.cfsb " +
                       " and ms_cf02.ypxh=yk_typk.ypxh and ms_cf02.ypxh=yk_ypcd.ypxh and yk_ypcd.ypcd=yk_cddz.ypcd " +
                       " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                       " group by yk_typk.ypmc,ms_cf02.yfgg,yk_typk.ypdw,yk_cddz.cdmc,ms_cf02.ypdj,ms_cf02.ypsl,ms_cf02.sbxh";


            //string s = "select ypmc,ypgg,ypdw,cdmc,ypdj,ypsl ,(ypsl*ypdj) as ypjg " +
            //            " from V_cf01_cf02_typk_ypcd_cddz where cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
            //            " group by ypmc,ypgg,ypdw,cdmc,ypdj,ypsl";

            if (conn.State != ConnectionState.Open) { conn.Open(); }

            using (SqlDataAdapter da_01 = new SqlDataAdapter(s, conn))
            {
                DataTable dt = new DataTable();

                da_01.Fill(dt);

                dataGridView2.DataSource = dt;

                dataGridView2.Columns[0].HeaderCell.Value = "药品名称";
                dataGridView2.Columns[1].HeaderCell.Value = "药品规格";
                dataGridView2.Columns[2].HeaderCell.Value = "药品单位";
                dataGridView2.Columns[3].HeaderCell.Value = "药品产地";
                dataGridView2.Columns[4].HeaderCell.Value = "药品单价";
                dataGridView2.Columns[5].HeaderCell.Value = "发药数量";
                dataGridView2.Columns[6].HeaderCell.Value = "药品价格";
                dataGridView2.Columns[7].HeaderCell.Value = "药库编码";
            }


        }

        private void button3_Click(object sender, EventArgs e)
        {
            //全选退药
            if (dataGridView2.Rows.Count == 0 && dataGridView1.Rows.Count == 0) { return; }
            for (int rowcount = 0; rowcount < dataGridView2.Rows.Count; rowcount++)
            {
                SqlParameter sp_01, sp_02, sp_03;
                string s = "select @ypxh=yk_typk.ypxh ,@ypsl=ms_cf02.ypsl,@sbxh=ms_cf02.sbxh from ms_cf01,ms_cf02,yk_typk,yk_ypcd,yk_cddz  where ms_cf01.cfsb=ms_cf02.cfsb  " +
                         " and ms_cf02.ypxh=yk_typk.ypxh  and ms_cf02.ypxh=yk_ypcd.ypxh  and yk_ypcd.ypcd=yk_cddz.ypcd  " +
                         " and ms_cf02.sbxh= '" + dataGridView2.CurrentRow.Cells[7].Value.ToString() + " '" +
                         " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                         " group by yk_typk.ypxh,ms_cf02.ypsl,ms_cf02.sbxh";

                if (conn.State != ConnectionState.Open) { conn.Open(); }

                using (SqlCommand cmd = new SqlCommand(s, conn))
                {
                    sp_01 = cmd.Parameters.Add("@ypxh", SqlDbType.NVarChar, 20);

                    sp_02 = cmd.Parameters.Add("@ypsl", SqlDbType.Decimal, 40);

                    sp_03 = cmd.Parameters.Add("@sbxh", SqlDbType.NVarChar, 40);

                    sp_01.Direction = ParameterDirection.Output;

                    sp_02.Direction = ParameterDirection.Output;

                    sp_03.Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();
                }


                string s_01 = "update yf_kcmx set  ypsl=ypsl+" + sp_02.Value.ToString() + " where  ypxh=" + sp_01.Value.ToString() + " ";



                using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                {
                    if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }

                    SqlCommand cmd_01 = new SqlCommand(s_01, conn_01);


                    cmd_01.ExecuteNonQuery();



                }

                string s_03 = "delete  from ms_cf02 where sbxh=" + sp_03.Value.ToString() + " ";

                using (SqlConnection conn_02 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                {
                    if (conn_02.State != ConnectionState.Open) { conn_02.Open(); }

                    SqlCommand cmd_02 = new SqlCommand(s_03, conn_02);

                    cmd_02.ExecuteNonQuery();
                }




            }


            MessageBox.Show("退药成功");
        }

        private void button1_Click(object sender, EventArgs e)
        {

            //单条药退药

            //dataGridView2.CurrentRow.Cells[0].Value.ToString();


            SqlParameter sp_01, sp_02, sp_03;

            if (dataGridView2.Rows.Count == 0 && dataGridView1.Rows.Count == 0) { return; }


            string s = "select @ypxh=yk_typk.ypxh ,@ypsl=ms_cf02.ypsl,@sbxh=ms_cf02.sbxh from ms_cf01,ms_cf02,yk_typk,yk_ypcd,yk_cddz where ms_cf01.cfsb=ms_cf02.cfsb  " +
                        " and ms_cf02.ypxh=yk_typk.ypxh  and ms_cf02.ypxh=yk_ypcd.ypxh  and yk_ypcd.ypcd=yk_cddz.ypcd  " +
                        " and ms_cf02.sbxh= '" + dataGridView2.CurrentRow.Cells[7].Value.ToString() + " '" +
                        " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                        " group by yk_typk.ypxh,ms_cf02.ypsl,ms_cf02.sbxh";

            if (conn.State != ConnectionState.Open) { conn.Open(); }

            using (SqlCommand cmd = new SqlCommand(s, conn))
            {
                sp_01 = cmd.Parameters.Add("@ypxh", SqlDbType.NVarChar, 20);

                sp_02 = cmd.Parameters.Add("@ypsl", SqlDbType.Decimal, 40);

                sp_03 = cmd.Parameters.Add("@sbxh", SqlDbType.NVarChar, 40);

                sp_01.Direction = ParameterDirection.Output;

                sp_02.Direction = ParameterDirection.Output;

                sp_03.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
            }



            string s_01 = "update yf_kcmx set ypsl=ypsl+" + sp_02.Value.ToString() + " where ypxh=" + sp_01.Value.ToString() + " ";



            using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }

                SqlCommand cmd_01 = new SqlCommand(s_01, conn_01);

                if (MessageBox.Show("您是否确定退掉" + dataGridView2.CurrentRow.Cells[0].Value.ToString() + "药？？", "温馨的提示", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
                {

                    cmd_01.ExecuteNonQuery();

                }
                else { return; }
                MessageBox.Show("退药成功");
                
            }

            string s_03 = "delete  from ms_cf02 where sbxh=" + sp_03.Value.ToString() + " ";

            using (SqlConnection conn_02 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_02.State != ConnectionState.Open) { conn_02.Open(); }

                SqlCommand cmd_02 = new SqlCommand(s_03, conn_02);

                cmd_02.ExecuteNonQuery();
            }







        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void ms_tycl_Load(object sender, EventArgs e)
        {

        }

        private void dataGridView1_SelectionChanged_1(object sender, EventArgs e)
        {
            //MessageBox.Show(dataGridView1.CurrentRow.Cells[0].Value.ToString());

            if (dataGridView1.Rows.Count == 0) { return; }
            string s = "select yk_typk.ypmc,ms_cf02.yfgg ,yk_typk.ypdw, yk_cddz.cdmc,ms_cf02.ypdj,ms_cf02.ypsl," +
                       "sum(ms_cf02.ypsl*ms_cf02.ypdj) as ypjg ,ms_cf02.sbxh as sbxh" +
                       " from ms_cf01,ms_cf02,yk_typk,yk_ypcd,yk_cddz where ms_cf01.cfsb=ms_cf02.cfsb " +
                       " and ms_cf02.ypxh=yk_typk.ypxh and ms_cf02.ypxh=yk_ypcd.ypxh and yk_ypcd.ypcd=yk_cddz.ypcd " +
                       " and ms_cf01.cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
                       " group by yk_typk.ypmc,ms_cf02.yfgg,yk_typk.ypdw,yk_cddz.cdmc,ms_cf02.ypdj,ms_cf02.ypsl,ms_cf02.sbxh";


            //string s = "select ypmc,ypgg,ypdw,cdmc,ypdj,ypsl ,(ypsl*ypdj) as ypjg " +
            //            " from V_cf01_cf02_typk_ypcd_cddz where cfhm=" + dataGridView1.CurrentRow.Cells[0].Value.ToString() +
            //            " group by ypmc,ypgg,ypdw,cdmc,ypdj,ypsl";

            if (conn.State != ConnectionState.Open) { conn.Open(); }

            using (SqlDataAdapter da_01 = new SqlDataAdapter(s, conn))
            {
                DataTable dt = new DataTable();

                da_01.Fill(dt);

                dataGridView2.DataSource = dt;

                dataGridView2.Columns[0].HeaderCell.Value = "药品名称";
                dataGridView2.Columns[1].HeaderCell.Value = "药品规格";
                dataGridView2.Columns[2].HeaderCell.Value = "药品单位";
                dataGridView2.Columns[3].HeaderCell.Value = "药品产地";
                dataGridView2.Columns[4].HeaderCell.Value = "药品单价";
                dataGridView2.Columns[5].HeaderCell.Value = "发药数量";
                dataGridView2.Columns[6].HeaderCell.Value = "药品价格";
                dataGridView2.Columns[7].HeaderCell.Value = "药库编码";
            }


        }


    }
}
